package org.pentaho.di.ui.spoon.dialog;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @ClassName: OracleUtilImpl
 * @Description: implements GerneralSqlUtil and named it end with the tailname
 * @ sql:
 create or replace trigger INCRE_ZB_TEST1_ttttttt after insert or update or delete on ZB_TEST1 for each row  
 declare
 VDML_TYPE CHAR(1);
 begin
 if inserting then
 VDML_TYPE := 'I';
 elsif updating then
 VDML_TYPE := 'U';
 elsif deleting then
 VDML_TYPE := 'D';
 end if;

 if deleting then
 insert into INCRE_ZB_TEST1_LT
 (ID, NAMEA, SIP_PK_ID, SIP_EXTYPE, SIP_DML_TYPE, SIP_EXCUTIME)
 values
 (:old.ID,
 :old.NAMEA,
 1 + (select NVL(MAX(SIP_PK_ID),'0') from INCRE_ZB_TEST1_LT),
 '0',
 VDML_TYPE,
 to_char(systimestamp, 'yyyymmddhh24missff'));
 else
 insert into INCRE_ZB_TEST1_LT
 (ID, NAMEA, SIP_PK_ID, SIP_EXTYPE, SIP_DML_TYPE, SIP_EXCUTIME)
 values
 (:new.ID,
 :new.NAMEA,
 1+ (select NVL(MAX(SIP_PK_ID),'0') from INCRE_ZB_TEST1_LT),
 '0',
 VDML_TYPE,
 to_char(systimestamp, 'yyyymmddhh24missff'));
 end if;
 end;
 * @author zhangbinp
 * @date single day~
 */
public class OracleUtilImpl implements GerneralSqlUtil {
	public String getTableColumnInfoSql(String tableName) {
		StringBuffer querySQL = new StringBuffer("select column_name ,data_type ,data_length from user_tab_columns  where table_name = '" + tableName.toUpperCase() + "'");
		return querySQL.toString();
	}

	public String getCreateLogTableSql(String tableName, ArrayList<String> columnNames, ArrayList<String> columnTypes) {

		StringBuffer sql = new StringBuffer(" create table " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + "( ");
		for (int i = 0; i < columnNames.size(); i++) {
			if (columnTypes.get(i).startsWith("BLOB")) {
				return "ORA-ERROR";
			}
			if (columnTypes.get(i).startsWith("DATE")) {
				columnTypes.set(i, "DATE");
			}
			if (columnTypes.get(i).startsWith("LONG") || columnTypes.get(i).startsWith("LONG RAW")) {
				return "ORA-ERROR";
			}
			if (columnTypes.get(i).startsWith("NUMBER")) {
				columnTypes.set(i, "NUMBER");
			}
			if (columnTypes.get(i).startsWith("CLOB")) {
				columnTypes.set(i, "CLOB");
			}
			sql.append(columnNames.get(i) + " " + columnTypes.get(i) + ",");
		}
		for (int i = 0; i < INFO_TABLE_FIELDS_ORACLE.length; i++) {
			sql.append(INFO_TABLE_FIELDS_ORACLE[i]);
		}
		return sql.toString();
	}

	public ArrayList<String> getCreateTriggerSql(String tableName, ArrayList<String> columnNames) {
		ArrayList<String> sqllist = new ArrayList<String>();
		StringBuffer sql = new StringBuffer(" create or replace trigger " + TRIGGER_HEAD_NAME_ORACLE + tableName + TRIGGER_TAIL_NAME_ORACLE + " after insert or update or delete on " + tableName + " for each row ");
		sql.append(" declare VDML_TYPE CHAR(1);");
		sql.append(" begin ");
		sql.append(" if inserting then VDML_TYPE := 'I'; ");
		sql.append(" elsif updating then VDML_TYPE := 'U'; ");
		sql.append(" elsif deleting then  VDML_TYPE := 'D'; ");
		sql.append(" end if; ");
		sql.append(" if deleting then ");
		sql.append(" insert into " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + " ( ");
		for (int i = 0; i < columnNames.size(); i++) {
			sql.append(columnNames.get(i) + ",");
		}
		sql.append("SIP_PK_ID,SIP_EXTYPE,SIP_DML_TYPE,SIP_EXCUTIME) ");
		sql.append(" values(");
		for (int j = 0; j < columnNames.size(); j++) {
			sql.append(":old." + columnNames.get(j) + ",");
		}
		sql.append(" 1 + (select NVL(MAX(SIP_PK_ID),'0') from " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + "),");
		sql.append(" '0',VDML_TYPE, to_char(systimestamp,'yyyymmddhh24missff'));");
		sql.append(" else ");
		sql.append(" insert into " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + " ( ");
		for (int m = 0; m < columnNames.size(); m++) {
			sql.append(columnNames.get(m) + ",");
		}
		sql.append("SIP_PK_ID,SIP_EXTYPE,SIP_DML_TYPE,SIP_EXCUTIME) ");
		sql.append(" values(");
		for (int n = 0; n < columnNames.size(); n++) {
			sql.append(":new." + columnNames.get(n) + ",");
		}
		sql.append(" 1 + (select NVL(MAX(SIP_PK_ID),'0') from " + LOG_TABLE_HEAD_NAME + tableName + LOG_TABLE_TAIL_NAME + "),");
		sql.append(" '0',VDML_TYPE, to_char(systimestamp,'yyyymmddhh24missff')); ");
		sql.append(" end if; ");
		sql.append(" end; ");
		sqllist.add(sql.toString());
		return sqllist;
	}

	/*public static void main(String[] args) {
		OracleUtilImpl oti = new OracleUtilImpl();
		String sql = oti.getTableColumnInfoSql("sip_sip_send_pack_log");
		System.err.println(sql);
	}*/

	public String getQueryUserTablesSql() {
		String sql = "select table_name from user_tables order by table_name";
		return sql;
	}

	public ArrayList<ArrayList<String>> getTargetColumnDDL(Connection conn, String tableName) throws Exception {
		ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
		ArrayList<String> columnNames = new ArrayList<String>();
		ArrayList<String> columnTypes = new ArrayList<String>();

		Statement query = conn.createStatement();
		ResultSet rs = query.executeQuery(getTableColumnInfoSql(tableName));
		while (rs.next()) {
			columnNames.add(rs.getString(1));
			columnTypes.add(rs.getString(2) + "(" + rs.getString(3) + ")");
		}
		result.add(columnNames);
		result.add(columnTypes);
		return result;
	}

	@Override
	public String getCreatEexcTableInfoSql() {
		String sql = " create table " + GerneralSqlUtil.lOGTABLE_INFO_TABLE_NAME + "(TABLENAME varchar2(255),TABLEDESC varchar2(255)) ";
		return sql;
	}

	@Override
	public PrivResult getTriggerPrivs(Connection conn, String username) throws SQLException {
		PrivResult result = new PrivResult();
		result.setDatabaseType("Oracle");
		String sql = "select * from session_privs t where t.privilege like 'CREATE T%'";
		Statement stm = conn.createStatement();
		ResultSet res = stm.executeQuery(sql);
		while (res.next()) {
			String col = res.getString(1);
			if (col.equals("CREATE TABLE")) {
				result.setCreate_priv(true);
			}
			if (col.equals("CREATE TRIGGER")) {
				result.setTrigger_priv(true);
			}
		}
		return result;
	}
}
